#/bin/python
import sys
import MySQLdb # sudo apt-get install python-mysqldb
from time import time

# connect to mysql database
try:
	conn = MySQLdb.connect(host='127.0.0.1', user='root', passwd='admin', db='recommendationengine', init_command='set names utf8')
	cursor = conn.cursor()
except MySQLdb.Error, e:
	print "Error %d: %s" % (e.args[0], e.args[1])
	sys.exit (1)

# def a dump function, which update the data
def dump_sql(offset, step, cursor):
	try:
		sql = "select userid,typeid,tag,programid,viewtime from userviewhistory limit %d,%d" % (offset, step)
		cursor.execute(sql)
	except MySQLdb.Error, e:
		print "Error %d: %s" % (e.args[0], e.args[1])
		sys.exit (1)
	rows = cursor.fetchall()
	res = ''
	global map_userid
	global len_userid
	global map_programid
	global len_programid
	for row in rows:
		if row[0] in map_userid:
			userid = map_userid[row[0]]
		else:
			len_userid += 1
			map_userid[row[0]] =  len_userid
			userid = len_userid
		if row[3] in map_programid:
			programid = map_programid[row[3]]
		else:
			len_programid += 1
			map_programid[row[3]] = len_programid
			programid = len_programid
		res += '%s,%s,%s,%s,%s' % (userid,row[1],row[2],programid,row[4]) + '\r\n'
	del rows
	return res

f = file('userviewhistory_new.csv', 'w')
step = 10000
total = 50001
map_userid = {}
len_userid = 0
map_programid = {}
len_programid = 0
for i in range(0, total, step):
	t = time()
	f.write(dump_sql(i, step, cursor))
	print time()-t
#	print time.strftime('%Y-%m-%d %X', time.localtime())
#	print '%d+%d ID is completed, total %d' % (i, step, total)
#        sys.stdout.flush()
else:
	fm = file('map_userid.csv', 'w')
	fm.write(''.join([('%s,%s' % (userid, map_userid[userid]) + '\r\n') for userid in map_userid]))
	fm.close()

	fm = file('map_programid.csv', 'w')
	fm.write(''.join([('%s,%s' % (programid, map_programid[programid]) + '\r\n') for programid in map_programid]))
	fm.close()
#	print 'The task is completed.'
f.close()
